using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
using WPFExcelReport.Controls;
namespace WPFExcelReport
{
    public partial class VETFWA2
    {
        public DataRow Convert2ETFWA2(DataRow dwInput, string ITXDBVERSION, bool IsConvert = false)
        {
            DataSet SResult = new DataSet();
            SResult = QueryETFWA2(dwInput, 0);
            DataRow dwReturn = null;
            if (SResult.Tables[0].Rows.Count > 0)
            {
                dwReturn = SResult.Tables[0].Rows[0];
            }
            else
            {
                dwReturn = SResult.Tables[0].NewRow();
            }
            switch (ITXDBVERSION)
            {
                case "YGR":
                    #region=========
         dwReturn["EWASTKNR"] = " ".PadRight(12 ); 
         dwReturn["EWASA"] = " ".PadRight(2 ); 
         dwReturn["EWAART"] = " ".PadRight(10 ); 
         dwReturn["EWAARTBEZ"] = " ".PadRight(12 ); 
         dwReturn["EWADESS"] = " ".PadRight(4 ); 
         dwReturn["EWAFPART"] = " ".PadRight(11 ); 
         dwReturn["EWAFARBE"] = " ".PadRight(10 ); 
         dwReturn["EWABESCHICH"] = " ".PadRight(5 ); 
         dwReturn["EWAAUSART"] = " ".PadRight(2 ); 
         dwReturn["EWADIREKT"] = " ".PadRight(1 ); 
         dwReturn["FILLER1"] = " ".PadRight(31 ); 
         dwReturn["EWASSTKNR"] = " ".PadRight(10 ); 
         dwReturn["EWASTKLG"] = " ".PadRight(5 ); 
         dwReturn["EWABRT"] = " ".PadRight(3 ); 
         dwReturn["EWATAXE"] = " ".PadRight(3 ); 
         dwReturn["EWASBRTANG"] = " ".PadRight(3 ); 
         dwReturn["EWAZENSUR"] = " ".PadRight(2 ); 
         dwReturn["EWAAUFTRPOS1"] = " ".PadRight(3 ); 
         dwReturn["EWAAUFTRPOS2"] = " ".PadRight(3 ); 
         dwReturn["EWAAUFTRPOS3"] = " ".PadRight(3 ); 
         dwReturn["EWAAUFTRPOS4"] = " ".PadRight(3 ); 
         dwReturn["EWAAUFTRPOS5"] = " ".PadRight(3 ); 
         dwReturn["EWAAUFTRPOS6"] = " ".PadRight(3 ); 
         dwReturn["EWAAUFTRPOS7"] = " ".PadRight(3 ); 
         dwReturn["EWAAUFTRPOS8"] = " ".PadRight(3 ); 
         dwReturn["EWAAUFTRPOS9"] = " ".PadRight(3 ); 
         dwReturn["EWAAUFTRPOS10"] = " ".PadRight(3 ); 
         dwReturn["EWAAUFTRPOS11"] = " ".PadRight(3 ); 
         dwReturn["EWAAUFTRPOS12"] = " ".PadRight(3 ); 
         dwReturn["EWAAUFTRPOS13"] = " ".PadRight(3 ); 
         dwReturn["EWAAUFTRPOS14"] = " ".PadRight(3 ); 
         dwReturn["EWAAUFTRPOS15"] = " ".PadRight(3 ); 
         dwReturn["EWAAUFTRPOS16"] = " ".PadRight(3 ); 
         dwReturn["EWAAUFTRPOS17"] = " ".PadRight(3 ); 
         dwReturn["EWAAUFTRPOS18"] = " ".PadRight(3 ); 
         dwReturn["EWAAUFTRPOS19"] = " ".PadRight(3 ); 
         dwReturn["EWAAUFTRPOS20"] = " ".PadRight(3 ); 
         dwReturn["EWALFDPOS"] = " ".PadRight(3 ); 
         dwReturn["EWAAPOS"] = " ".PadRight(4 ); 
         dwReturn["EWAAUPOS"] = " ".PadRight(2 ); 
         dwReturn["FILLER2"] = " ".PadRight(16 ); 
         dwReturn["EWAFSTKNR"] = " ".PadRight(10 ); 
         dwReturn["EWAFEHLA"] = " ".PadRight(5 ); 
         dwReturn["EWAFEHLE"] = " ".PadRight(5 ); 
         dwReturn["EWAFEHLSL"] = " ".PadRight(3 ); 
         dwReturn["EWAFBRTANG"] = " ".PadRight(3 ); 
         dwReturn["EWAFOLGENR"] = " ".PadRight(2 ); 
         dwReturn["FILLER3"] = " ".PadRight(22 ); 
         dwReturn["EWAISTKLG"] = " ".PadRight(5 ); 
         dwReturn["EWABENID"] = " ".PadRight(3 ); 
         dwReturn["EWASCHICHT"] = " ".PadRight(1 ); 
         dwReturn["EWAANLAGE"] = " ".PadRight(1 ); 
         dwReturn["EWADATUM"] = " ".PadRight(6 ); 
         dwReturn["EWAZEIT"] = " ".PadRight(4 ); 
         dwReturn["EWALFDTAG"] = " ".PadRight(3 ); 
         dwReturn["FILLER4"] = " ".PadRight(27 ); 
                    #endregion
                    break;
                case "JLT":

                    break;
                default:

                    break;
            }
            if (IsConvert)
            {
                foreach (DataColumn cm in dwInput.Table.Columns)
                {
                    if (dwInput[cm.ColumnName].ToString() == "")
                        continue;
                    if (dwReturn.Table.Columns.Contains(cm.ColumnName))
                    {
                        dwReturn[cm.ColumnName] = dwInput[cm.ColumnName];
                    }
                }
            }
            return dwReturn;
        }
        public void MapETFWA2(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveETFWA2(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            setResult = QueryETFWA2(dwData);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }
            MapETFWA2(ref dwLocal, dwData);

            if (!flgExist)
            {

                InsertETFWA2(dwLocal, ITXDBVERSION);
            }
            UpdateETFWA2(dwLocal);
        }
        public void InsertETFWA2(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (ITXDBVERSION)
            {
                case "YGR":
                    or1.CommandText = "INSERT INTO ETFWA2(VAKBMANDANT, VAKBKOLL, VAKBART, VAKBKUNDNR, VAKBFARBE, VAKBKDARTNR, VAKBKDARTBEZ, VAKBKDFARBNR, VAKBANDRUCK, VAKBMEGEW, VAKBMENGEH, VAKBGEWVON, VAKBGEWBIS, VAKBGEWCA, VAKBGEWFKT, VAKBGEWGRP, VAKBMEGFKT, VAKBMEGGRP, VAKBAENDZL, VAKBAENDBEN, VAKBAENDDAT, VAKBNEUBEN, VAKBNEUDAT, VAKBFILLER)"
                        + " VALUES (:VAKBMANDANT, :VAKBKOLL, :VAKBART, :VAKBKUNDNR, :VAKBFARBE, '                         ', '                         ', '                             ', ' ', '   ', '   ', 0, 0, ' ', '000000000000', ' ', '000000000000', ' ', 0, '        ', '000000', '        ', '      ', '                                          ')";
                    or1.Parameters.Add("VAKBMANDANT", OracleType.NVarChar).Value = dwData["VAKBMANDANT"].ToString();
                    or1.Parameters.Add("VAKBKOLL", OracleType.NVarChar).Value = dwData["VAKBKOLL"].ToString();
                    or1.Parameters.Add("VAKBART", OracleType.NVarChar).Value = dwData["VAKBART"].ToString();
                    or1.Parameters.Add("VAKBFARBE", OracleType.NVarChar).Value = dwData["VAKBFARBE"].ToString();
                    or1.Parameters.Add("VAKBKUNDNR", OracleType.NVarChar).Value = dwData["VAKBKUNDNR"].ToString();
                    break;
                //VAFMANDANT VAFKOLL VAFART VAFFARBE VAFBEZ VAFFFARBE

            }
            
            Connect2Proxy(or1);
        }
        public DataSet QueryETFWA2(DataRow dwData, int option = 1)
        {
            DataSet Return;
            DataSet Result = new DataSet();
            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 0:
                    or1.CommandText = "SELECT * FROM ETFWA2 WHERE  ROWNUM<2 ";
                    break;
              
                case 99:
                    or1.CommandText = "SELECT * FROM ETFWA2  WHERE ROWNUM<50";
                    break;
                default:
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
                Result.Tables[0].TableName = "ETFWA2";
            Return = Result;
            return Return;
        }
        public void DeleteETFWA2(DataRow dwData, int option = 1)
        {

            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM ETFWA2   WHERE  VAKBMANDANT=:VAKBMANDANT AND VAKBKOLL=:VAKBKOLL AND VAKBART =:VAKBART AND VAKBFARBE=:VAKBFARBE AND VAKBKUNDNR=:VAKBKUNDNR";
                     or1.Parameters.Add("VAKBMANDANT", OracleType.NVarChar).Value = dwData["VAKBMANDANT"].ToString();
                    or1.Parameters.Add("VAKBKOLL", OracleType.NVarChar).Value = dwData["VAKBKOLL"].ToString();
                    or1.Parameters.Add("VAKBART", OracleType.NVarChar).Value = dwData["VAKBART"].ToString();
                    or1.Parameters.Add("VAKBFARBE", OracleType.NVarChar).Value = dwData["VAKBFARBE"].ToString();
                    or1.Parameters.Add("VAKBKUNDNR", OracleType.NVarChar).Value = dwData["VAKBKUNDNR"].ToString();
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        } 
      
        public void UpdateETFWA2(DataRow dwData, string ITXDBVERSION = "50.24", int option = 1)
        {
            OracleCommand or1;
            DataSet Result = new DataSet();
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    #region====Command==== 
                    or1.CommandText = "UPDATE ETFWA2 SET "
                    +"EWASTKNR= :EWASTKNR,"
                    +"EWASA= :EWASA,"
                    +"EWAART= :EWAART,"
                    +"EWAARTBEZ= :EWAARTBEZ,"
                    +"EWADESS= :EWADESS,"
                    +"EWAFPART= :EWAFPART,"
                    +"EWAFARBE= :EWAFARBE,"
                    +"EWABESCHICH= :EWABESCHICH,"
                    +"EWAAUSART= :EWAAUSART,"
                    +"EWADIREKT= :EWADIREKT,"
                    +"FILLER1= :FILLER1,"
                    +"EWASSTKNR= :EWASSTKNR,"
                    +"EWASTKLG= :EWASTKLG,"
                    +"EWABRT= :EWABRT,"
                    +"EWATAXE= :EWATAXE,"
                    +"EWASBRTANG= :EWASBRTANG,"
                    +"EWAZENSUR= :EWAZENSUR,"
                    +"EWAAUFTRPOS1= :EWAAUFTRPOS1,"
                    +"EWAAUFTRPOS2= :EWAAUFTRPOS2,"
                    +"EWAAUFTRPOS3= :EWAAUFTRPOS3,"
                    +"EWAAUFTRPOS4= :EWAAUFTRPOS4,"
                    +"EWAAUFTRPOS5= :EWAAUFTRPOS5,"
                    +"EWAAUFTRPOS6= :EWAAUFTRPOS6,"
                    +"EWAAUFTRPOS7= :EWAAUFTRPOS7,"
                    +"EWAAUFTRPOS8= :EWAAUFTRPOS8,"
                    +"EWAAUFTRPOS9= :EWAAUFTRPOS9,"
                    +"EWAAUFTRPOS10= :EWAAUFTRPOS10,"
                    +"EWAAUFTRPOS11= :EWAAUFTRPOS11,"
                    +"EWAAUFTRPOS12= :EWAAUFTRPOS12,"
                    +"EWAAUFTRPOS13= :EWAAUFTRPOS13,"
                    +"EWAAUFTRPOS14= :EWAAUFTRPOS14,"
                    +"EWAAUFTRPOS15= :EWAAUFTRPOS15,"
                    +"EWAAUFTRPOS16= :EWAAUFTRPOS16,"
                    +"EWAAUFTRPOS17= :EWAAUFTRPOS17,"
                    +"EWAAUFTRPOS18= :EWAAUFTRPOS18,"
                    +"EWAAUFTRPOS19= :EWAAUFTRPOS19,"
                    +"EWAAUFTRPOS20= :EWAAUFTRPOS20,"
                    +"EWALFDPOS= :EWALFDPOS,"
                    +"EWAAPOS= :EWAAPOS,"
                    +"EWAAUPOS= :EWAAUPOS,"
                    +"FILLER2= :FILLER2,"
                    +"EWAFSTKNR= :EWAFSTKNR,"
                    +"EWAFEHLA= :EWAFEHLA,"
                    +"EWAFEHLE= :EWAFEHLE,"
                    +"EWAFEHLSL= :EWAFEHLSL,"
                    +"EWAFBRTANG= :EWAFBRTANG,"
                    +"EWAFOLGENR= :EWAFOLGENR,"
                    +"FILLER3= :FILLER3,"
                    +"EWAISTKLG= :EWAISTKLG,"
                    +"EWABENID= :EWABENID,"
                    +"EWASCHICHT= :EWASCHICHT,"
                    +"EWAANLAGE= :EWAANLAGE,"
                    +"EWADATUM= :EWADATUM,"
                    +"EWAZEIT= :EWAZEIT,"
                    +"EWALFDTAG= :EWALFDTAG,"
                    +"FILLER4= :FILLER4,"
                        + " WHERE  "
                        + "  VAKBMANDANT=:VAKBMANDANT AND VAKBKOLL=:VAKBKOLL AND VAKBART =:VAKBART AND VAKBFARBE=:VAKBFARBE AND VAKBKUNDNR=:VAKBKUNDNR";
                    #endregion

                    #region====Parameters====
          or1.Parameters.Add("EWASTKNR", OracleType.NVarChar).Value = dwData["EWASTKNR"].ToString(); 
          or1.Parameters.Add("EWASA", OracleType.NVarChar).Value = dwData["EWASA"].ToString(); 
          or1.Parameters.Add("EWAART", OracleType.NVarChar).Value = dwData["EWAART"].ToString(); 
          or1.Parameters.Add("EWAARTBEZ", OracleType.NVarChar).Value = dwData["EWAARTBEZ"].ToString(); 
          or1.Parameters.Add("EWADESS", OracleType.NVarChar).Value = dwData["EWADESS"].ToString(); 
          or1.Parameters.Add("EWAFPART", OracleType.NVarChar).Value = dwData["EWAFPART"].ToString(); 
          or1.Parameters.Add("EWAFARBE", OracleType.NVarChar).Value = dwData["EWAFARBE"].ToString(); 
          or1.Parameters.Add("EWABESCHICH", OracleType.NVarChar).Value = dwData["EWABESCHICH"].ToString(); 
          or1.Parameters.Add("EWAAUSART", OracleType.NVarChar).Value = dwData["EWAAUSART"].ToString(); 
          or1.Parameters.Add("EWADIREKT", OracleType.NVarChar).Value = dwData["EWADIREKT"].ToString(); 
          or1.Parameters.Add("FILLER1", OracleType.NVarChar).Value = dwData["FILLER1"].ToString(); 
          or1.Parameters.Add("EWASSTKNR", OracleType.NVarChar).Value = dwData["EWASSTKNR"].ToString(); 
          or1.Parameters.Add("EWASTKLG", OracleType.NVarChar).Value = dwData["EWASTKLG"].ToString(); 
          or1.Parameters.Add("EWABRT", OracleType.NVarChar).Value = dwData["EWABRT"].ToString(); 
          or1.Parameters.Add("EWATAXE", OracleType.NVarChar).Value = dwData["EWATAXE"].ToString(); 
          or1.Parameters.Add("EWASBRTANG", OracleType.NVarChar).Value = dwData["EWASBRTANG"].ToString(); 
          or1.Parameters.Add("EWAZENSUR", OracleType.NVarChar).Value = dwData["EWAZENSUR"].ToString(); 
          or1.Parameters.Add("EWAAUFTRPOS1", OracleType.NVarChar).Value = dwData["EWAAUFTRPOS1"].ToString(); 
          or1.Parameters.Add("EWAAUFTRPOS2", OracleType.NVarChar).Value = dwData["EWAAUFTRPOS2"].ToString(); 
          or1.Parameters.Add("EWAAUFTRPOS3", OracleType.NVarChar).Value = dwData["EWAAUFTRPOS3"].ToString(); 
          or1.Parameters.Add("EWAAUFTRPOS4", OracleType.NVarChar).Value = dwData["EWAAUFTRPOS4"].ToString(); 
          or1.Parameters.Add("EWAAUFTRPOS5", OracleType.NVarChar).Value = dwData["EWAAUFTRPOS5"].ToString(); 
          or1.Parameters.Add("EWAAUFTRPOS6", OracleType.NVarChar).Value = dwData["EWAAUFTRPOS6"].ToString(); 
          or1.Parameters.Add("EWAAUFTRPOS7", OracleType.NVarChar).Value = dwData["EWAAUFTRPOS7"].ToString(); 
          or1.Parameters.Add("EWAAUFTRPOS8", OracleType.NVarChar).Value = dwData["EWAAUFTRPOS8"].ToString(); 
          or1.Parameters.Add("EWAAUFTRPOS9", OracleType.NVarChar).Value = dwData["EWAAUFTRPOS9"].ToString(); 
          or1.Parameters.Add("EWAAUFTRPOS10", OracleType.NVarChar).Value = dwData["EWAAUFTRPOS10"].ToString(); 
          or1.Parameters.Add("EWAAUFTRPOS11", OracleType.NVarChar).Value = dwData["EWAAUFTRPOS11"].ToString(); 
          or1.Parameters.Add("EWAAUFTRPOS12", OracleType.NVarChar).Value = dwData["EWAAUFTRPOS12"].ToString(); 
          or1.Parameters.Add("EWAAUFTRPOS13", OracleType.NVarChar).Value = dwData["EWAAUFTRPOS13"].ToString(); 
          or1.Parameters.Add("EWAAUFTRPOS14", OracleType.NVarChar).Value = dwData["EWAAUFTRPOS14"].ToString(); 
          or1.Parameters.Add("EWAAUFTRPOS15", OracleType.NVarChar).Value = dwData["EWAAUFTRPOS15"].ToString(); 
          or1.Parameters.Add("EWAAUFTRPOS16", OracleType.NVarChar).Value = dwData["EWAAUFTRPOS16"].ToString(); 
          or1.Parameters.Add("EWAAUFTRPOS17", OracleType.NVarChar).Value = dwData["EWAAUFTRPOS17"].ToString(); 
          or1.Parameters.Add("EWAAUFTRPOS18", OracleType.NVarChar).Value = dwData["EWAAUFTRPOS18"].ToString(); 
          or1.Parameters.Add("EWAAUFTRPOS19", OracleType.NVarChar).Value = dwData["EWAAUFTRPOS19"].ToString(); 
          or1.Parameters.Add("EWAAUFTRPOS20", OracleType.NVarChar).Value = dwData["EWAAUFTRPOS20"].ToString(); 
          or1.Parameters.Add("EWALFDPOS", OracleType.NVarChar).Value = dwData["EWALFDPOS"].ToString(); 
          or1.Parameters.Add("EWAAPOS", OracleType.NVarChar).Value = dwData["EWAAPOS"].ToString(); 
          or1.Parameters.Add("EWAAUPOS", OracleType.NVarChar).Value = dwData["EWAAUPOS"].ToString(); 
          or1.Parameters.Add("FILLER2", OracleType.NVarChar).Value = dwData["FILLER2"].ToString(); 
          or1.Parameters.Add("EWAFSTKNR", OracleType.NVarChar).Value = dwData["EWAFSTKNR"].ToString(); 
          or1.Parameters.Add("EWAFEHLA", OracleType.NVarChar).Value = dwData["EWAFEHLA"].ToString(); 
          or1.Parameters.Add("EWAFEHLE", OracleType.NVarChar).Value = dwData["EWAFEHLE"].ToString(); 
          or1.Parameters.Add("EWAFEHLSL", OracleType.NVarChar).Value = dwData["EWAFEHLSL"].ToString(); 
          or1.Parameters.Add("EWAFBRTANG", OracleType.NVarChar).Value = dwData["EWAFBRTANG"].ToString(); 
          or1.Parameters.Add("EWAFOLGENR", OracleType.NVarChar).Value = dwData["EWAFOLGENR"].ToString(); 
          or1.Parameters.Add("FILLER3", OracleType.NVarChar).Value = dwData["FILLER3"].ToString(); 
          or1.Parameters.Add("EWAISTKLG", OracleType.NVarChar).Value = dwData["EWAISTKLG"].ToString(); 
          or1.Parameters.Add("EWABENID", OracleType.NVarChar).Value = dwData["EWABENID"].ToString(); 
          or1.Parameters.Add("EWASCHICHT", OracleType.NVarChar).Value = dwData["EWASCHICHT"].ToString(); 
          or1.Parameters.Add("EWAANLAGE", OracleType.NVarChar).Value = dwData["EWAANLAGE"].ToString(); 
          or1.Parameters.Add("EWADATUM", OracleType.NVarChar).Value = dwData["EWADATUM"].ToString(); 
          or1.Parameters.Add("EWAZEIT", OracleType.NVarChar).Value = dwData["EWAZEIT"].ToString(); 
          or1.Parameters.Add("EWALFDTAG", OracleType.NVarChar).Value = dwData["EWALFDTAG"].ToString(); 
          or1.Parameters.Add("FILLER4", OracleType.NVarChar).Value = dwData["FILLER4"].ToString(); 
                    #endregion
                    break; 
                default:
                    break;
            }


            Connect2Proxy(or1);
        }
    }
}
